package com.yulian.web_api.supplier_management;
 

import java.util.*;

import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.yulian.repository.CommonRepositoryImpl;

import com.yulian.util.CommonUtils; 
import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.auth.User;
import io.vertx.pgclient.PgPool;
import io.vertx.sqlclient.Tuple;

/**
 * 该类位于业务逻辑层，主要实现合伙人提成比例数据查询，新增数据，更新数据的业务逻辑，为MainVerticle中的REST接口提供服务
 *
 * @author 汪俊峰 刘涛 余大勇
 */
public class PartnerCommissionRatioManager {
    final InternalLogger logger = Log4JLoggerFactory.getInstance(PartnerCommissionRatioManager.class);
    CommonRepositoryImpl commonRepository = new CommonRepositoryImpl(); 
    /**
     * 根据传入的查询参数获取满足条件的记录
     *
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @return 满足条件的记录
     */
    public Future<JsonObject> getRecords(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        /**
         * 获取操作类型字段的值,各字段注释请参看数据库设计文档中数据表partner_commission_ratio的说明
			id	序号
			site_id	销售地区编码
			class_lv1_id	商品一级分类编码
			gross_margin_min	到岸价毛利率区间最小值
			gross_margin_max	到岸价毛利率区间最大值
			gross_margin_range	到岸价毛利率区间
			supply_partner_commission_ratio	供应链合伙人毛利提成比例
			processing_manager_commission_ratio	加工中心管理人毛利提成比例
			sale_partner_commission_ratio	销售合伙人毛利提成比例
			is_delete	生效/失效  
         */
        // 验证必须传递的字段 
        List<String> must_fields = Arrays.asList("site_id_list", "class_lv1_id_list", "is_delete_list");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            promise.complete(must);
            return promise.future();
        }
        // 验证必须传的数值字段
 		List<String>  numeric_fields = Arrays.asList( "page_size", "page_index");
 		JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
 		if (numeric != null) {
 			promise.complete(numeric);  
 			return promise.future();
 		}
        //从bodyJson中获取数据 各字段注释请参看数据库设计文档中数据表partner_commission_ratio的说明
        String site_id_list = bodyJSObj.getString("site_id_list");
        String class_lv1_id_list = bodyJSObj.getString("class_lv1_id_list");
        String is_delete_list = bodyJSObj.getString("is_delete_list");
        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size; 
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();
        queryJSObj.put("site_id", site_id_list);
        queryJSObj.put("class_lv1_id", class_lv1_id_list);
        queryJSObj.put("is_delete", is_delete_list);

        //定义多个set，存储每一个 partner_commission_ratio 中多个code
        Set<String> site_id_list_set = new HashSet<String>();
        Set<String> class_lv1_id_list_set = new HashSet<String>();

        this.commonRepository.getCon(client).onSuccess(conn -> {
            String sql = createSelectSql(queryJSObj, query_offset, query_limit);
            this.commonRepository.queryJsonArray(conn, sql, "getPartnerCommissionRatioRecords")
            .onSuccess(resJSArray -> {
                //取出每条记录中含code字段的值放入对应的集合
                for (int i = 0; i < resJSArray.size(); i++) {
                    JsonObject jsonObj = resJSArray.getJsonObject(i);
                    site_id_list_set.add(jsonObj.getString("site_id"));
                    class_lv1_id_list_set.add(jsonObj.getString("class_lv1_id"));
                } 
                //获取满足条件的记录总数
                String count_sql = this.commonRepository.createCountSql(queryJSObj, "partner_commission_ratio");
                Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "get_partner_commission_ratio");

                //获取销售地区编码和名称
                String site_sql = "select distinct id, site_name from  site where id = any(array['" +
                String.join("','", site_id_list_set) + "']) ";
                Future<JsonObject> getSiteFuture = this.commonRepository.getCodeNamePairJsonObject(conn, site_sql, "site_name");
                //获取商品一级分类编码和名称
                String class_lv1_id_sql = "select distinct id,class_name from goods_class_lv1 where class_code = any(array['" +
                String.join("','", class_lv1_id_list_set) + "']) and is_delete=0";
                Future<JsonObject> getClassLv1Future = this.commonRepository.getCodeNamePairJsonObject(conn, class_lv1_id_sql, "get_class_lv1_name");

                CompositeFuture.join(Arrays.asList(getTotalFuture, getSiteFuture, getClassLv1Future)).onComplete(ar2 -> {
                    if (ar2.succeeded()) {
                        JsonObject site_info = new JsonObject();
                        JsonObject class_lv1_id_info = new JsonObject();

                        int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                        site_info = (JsonObject) ar2.result().list().get(1);
                        class_lv1_id_info = (JsonObject) ar2.result().list().get(2);

                        //从配置文件中读取
                        JsonObject is_delete_info = config.getJsonObject("DELETE_INFO"); 
                        // 最后组装含有name的Json对象和数组
                        JsonArray recordsJSArray = new JsonArray();
                        for (int i = 0; i < resJSArray.size(); i++) {
                            JsonObject record_obj = resJSArray.getJsonObject(i);
                            String site_id = record_obj.getString("site_id");
                            record_obj.put("site_name", site_info.getString(site_id));
                            String class_lv1_id = record_obj.getString("class_lv1_id");
                            record_obj.put("class_lv1_id_name", class_lv1_id_info.getString(class_lv1_id));
                            record_obj.put("is_delete_name", is_delete_info.getString(record_obj.getString("is_delete")));
                            recordsJSArray.add(record_obj);
                        }
                        JsonObject dataJSObj = new JsonObject();
                        dataJSObj.put("total", total_records).put("records", recordsJSArray);
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);

                        promise.complete(responseJSObj);
                    } else {
                        logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                        promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getPartnerCommissionRatioRecords", "获取合伙人提成比例失败"));
                    }
                });
            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getPartnerCommissionRatioRecords", "获取合伙人提成比例失败")));
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getPartnerCommissionRatioRecords", "数据库连接失败")));
        return promise.future();
    }
    /**
     * 构建查询数据表的sql语句
     * @param queryJS
     * @param query_offset
     * @param query_limit
     * @return
     */
    public String createSelectSql(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("site_id"), new CustomSql("class_lv1_id"), new CustomSql("gross_margin_min"),new CustomSql("gross_margin_max"),new CustomSql("gross_margin_range"),
        new CustomSql("supply_partner_commission_ratio"), new CustomSql("processing_manager_commission_ratio"), new CustomSql("sale_partner_commission_ratio"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
        .addCustomFromTable("partner_commission_ratio");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by update_time desc limit " + query_limit + " offset " + query_offset;
    }

    /**
     * 该方法实现接收web前端通过body传入的参数，调用通用类CommonRepositoryImpl的方法实现数据的增、删、改 。
     * 通过含参数的Json对象bodyJSObj，通过method字段获取操作类型，分别是insert，update,
     * 1) 操作类型为 insert， 新增数据，
     * 2) 操作类型为 update，根据id更新记录 
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @param user      操作用户
     * @return 操作的结果
     * @throws Exception
     */
    public Future<JsonObject> insertOrUpdate(PgPool client, JsonObject config, JsonObject bodyJSObj, User user) {
        Promise<JsonObject> promise = Promise.promise();
        int user_id = user.attributes().getJsonObject("accessToken").getInteger("userId");
        String create_user = user.attributes().getJsonObject("accessToken").getString("username"); 
        // 验证必须传递的字段
        List<String> must_fields = Arrays.asList("method", "site_id", "class_lv1_id","gross_margin_range");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            promise.complete(must);
            return promise.future();
        }  
        // 验证数值字段
 		List<String>  numeric_fields = Arrays.asList( "gross_margin_min","gross_margin_max", "supply_partner_commission_ratio", "processing_manager_commission_ratio", "sale_partner_commission_ratio", "id");
 		JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
 		if (numeric != null) {
 			promise.complete(numeric);  
 			return promise.future();
 		}
        //获取bodyJSObj参数对象中字段的值 
        /**
         * 获取操作类型字段的值,各字段注释请参看数据库设计文档中数据表partner_commission_ratio的说明
			id	序号
			site_id	销售地区编码
			class_lv1_id	商品一级分类编码
			gross_margin_min	到岸价毛利率区间最小值
			gross_margin_max	到岸价毛利率区间最大值
			gross_margin_range	到岸价毛利率区间
			supply_partner_commission_ratio	供应链合伙人毛利提成比例
			processing_manager_commission_ratio	加工中心管理人毛利提成比例
			sale_partner_commission_ratio	销售合伙人毛利提成比例
			is_delete	生效/失效  
         */
        String method = bodyJSObj.getString("method");
        String site_id = bodyJSObj.getString("site_id");
        String class_lv1_id = bodyJSObj.getString("class_lv1_id");
        Double gross_margin_min = bodyJSObj.getDouble("gross_margin_min");
        Double gross_margin_max = bodyJSObj.getDouble("gross_margin_max");
        String gross_margin_range = bodyJSObj.getString("gross_margin_range");
        Double supply_partner_commission_ratio = bodyJSObj.getDouble("supply_partner_commission_ratio");
        Double processing_manager_commission_ratio = bodyJSObj.getDouble("processing_manager_commission_ratio");
        Double sale_partner_commission_ratio = bodyJSObj.getDouble("sale_partner_commission_ratio"); 
        long id = Long.valueOf(bodyJSObj.getString("id"));
        //验证几个提成比例之和是否为1
        if( supply_partner_commission_ratio+processing_manager_commission_ratio+sale_partner_commission_ratio!=1) {
        	promise.fail(CommonUtils.createExceptionJsonObjectStr(-1, "insertOrUpdatePartnerCommissionRatio", "合伙人提成比例之和不等于100"));
        	return promise.future();
        }
        this.commonRepository.getCon(client).onSuccess(conn -> {
            /**
             * 校验多个code是否合法
             */
            //校验供销售地区code
            String site_id_sql = "select id, site_name from site where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> siteCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, site_id_sql, Tuple.of(site_id), "insert_partner_commission_ratio", "销售地区id 操作失败");
            //校验商品一级分类code
            String class_lv1_id_sql = "select id, class_name from goods_class_lv1 where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> classLv1CodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, class_lv1_id_sql, Tuple.of(class_lv1_id), "insert_partner_commission_ratio", "商品一级分类id 操作失败");
            if (method.equals("insert")) {  
            	 String partner_commission_ratio_sql = "select count(*) as total from partner_commission_ratio where site_id = $1 and class_lv1_id = $2 and gross_margin_range=$3";
                 Future<Integer> partnerCommissionRatioFuture = this.commonRepository.getRecordsCount(conn, partner_commission_ratio_sql, Tuple.of(site_id, class_lv1_id,gross_margin_range), "get_partner_commission_ratio");

                // 并行执行多个future
                CompositeFuture.join(Arrays.asList(partnerCommissionRatioFuture, siteCodeFuture, classLv1CodeFuture))
                .onComplete(ar -> {
                    if (ar.succeeded()) {
                        int total_records = Integer.valueOf(ar.result().list().get(0).toString());
                        if (total_records == 0) {
                            // 新增
                            String insert_sql = "INSERT INTO partner_commission_ratio(site_id, class_lv1_id, gross_margin_min, gross_margin_max, gross_margin_range, supply_partner_commission_ratio, processing_manager_commission_ratio, sale_partner_commission_ratio,create_user)"
                            + " VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)  RETURNING id";
                            this.commonRepository.insertOrUpdate(conn, insert_sql, Tuple.of(site_id, class_lv1_id, gross_margin_min, gross_margin_max,gross_margin_range, supply_partner_commission_ratio, processing_manager_commission_ratio, sale_partner_commission_ratio,user_id), "insert_partner_commission_ratio","新增合伙人提成比例")
                            .onSuccess(resultJSObj -> { 
                                //写入操作日志
                                this.commonRepository.writePlatformLog(conn, user_id,"新增", "partner_commission_ratio/insert", bodyJSObj, resultJSObj, "")
                                .onComplete(ar3 -> {
                                    promise.complete(resultJSObj);
                                });
                            }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "insertPartnerCommissionRatio", "新增合伙人提成比例基本信息失败")));

                        } else {
                            // 更新
                            String update_sql = "UPDATE partner_commission_ratio SET site_id = $1, class_lv1_id = $2, supply_partner_commission_ratio = $3, " +
                            "processing_manager_commission_ratio = $4, sale_partner_commission_ratio = $5,update_user = $6 WHERE site_id = $7 and class_lv1_id = $8 and gross_margin_range = $9 RETURNING id";
                            this.commonRepository.insertOrUpdate(conn, update_sql, Tuple.of(site_id, class_lv1_id, gross_margin_range, supply_partner_commission_ratio, 
                            processing_manager_commission_ratio, sale_partner_commission_ratio, user_id, site_id, class_lv1_id), "update_partner_commission_ratio","更新合伙人提成比例")
                            .onSuccess(resultJSObj -> { 
                                //写入操作日志
                                this.commonRepository.writePlatformLog(conn, user_id,"编辑", "partner_commission_ratio/insert", bodyJSObj, resultJSObj, "")
                                .onComplete(ar3 -> {
                                    promise.complete(resultJSObj);
                                });
                            }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "insertPartnerCommissionRatio", "新增合伙人提成比例基本信息失败")));

                        }
                    } else {
                        promise.fail(CommonUtils.createExceptionJsonObjectStr(ar, "insertPartnerCommissionRatio", "新增数据发生错误"));
                    }
                });
            } else if (method.equals("update")) {
            	String partner_commission_ratio_sql = "select count(*) as total from partner_commission_ratio where site_id = $1 and  class_lv1_id = $2 and gross_margin_range=$3 and id != $4";
                Future<Integer> partnerCommissionRatioFuture = this.commonRepository.getRecordsCount(conn, partner_commission_ratio_sql, Tuple.of(site_id, class_lv1_id,gross_margin_range, id), "get_partner_commission_ratio");
                // 并行执行多个future
                CompositeFuture.join(Arrays.asList(partnerCommissionRatioFuture, siteCodeFuture, classLv1CodeFuture))
                .onComplete(ar -> {
                    if (ar.succeeded()) {
                    	int total_records = Integer.valueOf(ar.result().list().get(0).toString());
                        if (total_records == 0) {
                        	String update_sql = "UPDATE partner_commission_ratio SET site_id = $1, class_lv1_id = $2, gross_margin_min = $3, gross_margin_max = $4, gross_margin_range = $5, supply_partner_commission_ratio = $6, " +
                            "processing_manager_commission_ratio = $7, sale_partner_commission_ratio = $8, update_user = $9 WHERE id = $10 RETURNING id";
                            this.commonRepository.insertOrUpdate(conn, update_sql, Tuple.of( site_id, class_lv1_id, gross_margin_min, gross_margin_max,gross_margin_range, supply_partner_commission_ratio, 
                            processing_manager_commission_ratio, sale_partner_commission_ratio, user_id, id), "update_partner_commission_ratio","更新合伙人提成比例")
                            .onSuccess(resultJSObj -> {  
                                //写入操作日志
                                this.commonRepository.writePlatformLog(conn, user_id,"编辑", "/web/partner_commission_ratio/update", bodyJSObj, resultJSObj, "")
                                .onComplete(ar3 -> {
                                    promise.complete(resultJSObj);
                                });
                            }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "updatePartnerCommissionRatio", "更新合伙人提成比例失败"))); 
                        }else {
                        	promise.fail(CommonUtils.createExceptionJsonObjectStr( -1, "updatePartnerCommissionRatio", "合伙人提成比例数据已经存在"));
                        } 
                    } else {
                        promise.fail(CommonUtils.createExceptionJsonObjectStr(ar, "updatePartnerCommissionRatio", "更新数据发生错误"));
                    }
                }); 
            }
        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "getPartnerCommissionRatioRecords", "数据库连接失败")));
        return promise.future(); 
    }
 
}

